package me.baicai.weixin.identity.repository;

import com.alibaba.fastjson.JSONObject;
import me.baicai.weixin.application.Result;
import me.baicai.weixin.entity.order.Order;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.List;


@Repository
public interface OrderRepository extends JpaRepository<Order, Long>
{

    Order getOrderById(String id);

    @Query(value = "select o.id,o.amount,o.time,o.payType,o.created,o.deviceSN,s.shopName,s.address from `order` o LEFT JOIN shop s on o.shopId = s.id where o.customerid= ?1 and o.orderstatus= ?2 and o.created between ?3 and date_add(?4, interval 1 day)", nativeQuery = true)
    List<JSONObject> findAllByCustomeridAndOrderstatus(String openid, Integer status,
                                                       String beginDate, String endDate,
                                                       Pageable pageable);

    Order findOrderByIdAndOrderstatus(String id, Integer status);

    List<Order> findOrderByCustomeridAndOrderstatus(String openid, Integer status);

    boolean existsByDevicesnAndOrderstatus(String sn, Integer status);

    boolean existsByCustomeridAndOrderstatus(String sn, Integer status);

    List<Order> findAllByOrderstatusAndEndBeforeOrOrderstatus(Integer orderstatus, Date end,
                                                              Integer status);


    Order findOrderByDevicesnAndOrderstatus(String sn, Integer status);

    Page<Order> findAllByIdLike(String valueOf, Pageable pageable);

    Page<Order> findAllByOrderstatus(String s, Pageable pageable);

    Page<Order> findAllByDevicesnLike(String sn, Pageable pageable);

    Page<Order> findAllByShopid(Integer shopid, Pageable pageable);

    Page<Order> findAllByCustomerid(String id, Pageable pageable);

    @Query(value = "select * from `order` where date(`end`) = ?1", nativeQuery = true)
    Page<Order> findAllByEnd(String time, Pageable pageable);

    @Query(value = "select * from `order` where date(created) = ?1", nativeQuery = true)
    Page<Order> findAllByCreate(String value, Pageable pageable);

    @Query(value = "select o.*, s.shopName from `order` o LEFT JOIN shop s on o.shopId = s.id where o.created between ?1 and date_add(?2,interval 1 day) order by o.orderNo desc limit ?3,20", nativeQuery = true)
    List<JSONObject> allOrders(String start, String end, int page);

    @Query(value = "select o.*, s.shopName from `order` o LEFT JOIN shop s on o.shopId = s.id where o.created between ?2 and date_add(?3,interval 1 day) and o.id like ?1 or s.shopName like ?1 or o.deviceSN like ?1 order by o.orderNo desc limit ?4,20", nativeQuery = true)
    List<JSONObject> allOrders(String s, String start, String end, int page);

    @Query(value = "select a.*, m.amount,m.remark from money_log m LEFT JOIN agent a on a.openid = m.targetId where m.orderId = ?1 and m.targetType = 'agent'", nativeQuery = true)
    List<JSONObject> findAgnetByOrderId(String id);

    @Query(value = "select s.*,m.amount,m.remark from money_log m LEFT JOIN shop s on s.id = m.shopid where m.orderId = ?1 and m.targetType = 'shop'", nativeQuery = true)
   JSONObject findShopByOrderId(String id);

    @Query(value = "select e.`name`, e.telphone, m.amount,m.remark from money_log m LEFT JOIN employee e on e.openid = m.targetId where m.orderId = ?1 and m.targetType in ('engineer','salesman')", nativeQuery = true)
    List<JSONObject> findEngByOrderId(String id);

    @Query(value = "select t.`name`, t.nickName, t. gender, t.`language`, t.type, o.amount, o.created from `order` o LEFT JOIN identity t on t.openid = o.customerId where o.id = ?1", nativeQuery = true)
    JSONObject findCustomerByOrderId(String id);


    @Query("select o from Order o where o.shopid in (select s.id from Shop s left join Agent a on s.agentid = a.id where a.id like ?1)")
    Page<Order> findAllByAgentid(Integer valueOf, Pageable pageable);

    @Query(value = "select count(o.id) from `order` o where YEARWEEK(date_format(o.`end`,'%Y-%m-%d')) = YEARWEEK(now()) and o.orderstatus = ?1", nativeQuery = true)
    Integer findAllByWeeklyOrderCount(Integer status);

    @Query(value = "select ifnull(sum(o.amount),0.00) from `order` o where YEARWEEK(date_format(o.`end`,'%Y-%m-%d')) = YEARWEEK(now()) and o.orderstatus = ?1", nativeQuery = true)
    Float findAllByWeeklyOrderAmount(Integer status);


    @Query(value = "select count(o.id) from `order` o where TO_DAYS(o.`end`) = TO_DAYS(now()) and o.orderstatus = ?1", nativeQuery = true)
    Integer findAllByNowOrderCount(Integer status);

    @Query(value = "select ifnull(sum(o.amount),0.00) from `order` o where TO_DAYS(o.`end`) = TO_DAYS(now()) and o.orderstatus = ?1", nativeQuery = true)
    Float findAllByNowOrderAmount(Integer status);

    @Query(value = "SELECT DATE_FORMAT(o.`end`,'%Y-%m-%d') as time,count(o.amount) as amount FROM `order` o where DATE_SUB(CURDATE(), INTERVAL ?2 DAY) <= date(o.`end`) and o.orderStatus = ?1 GROUP BY  DATE_FORMAT(o.`end`,'%Y-%m-%d') ORDER BY time", nativeQuery = true)
    List<JSONObject> sevenOrderCount(Integer status, Integer time);

    @Query(value = "SELECT DATE_FORMAT(o.`end`,'%Y-%m-%d') as time,ifnull(sum(o.amount),0.00) as amount FROM `order` o where DATE_SUB(CURDATE(), INTERVAL ?2 DAY) <= date(o.`end`) and o.orderStatus = ?1 GROUP BY  DATE_FORMAT(o.`end`,'%Y-%m-%d') ORDER BY time", nativeQuery = true)
    List<JSONObject> sevenOrderAmount(int status, int time);

    @Transactional
    @Modifying
    @Query(value = "delete from `order` where id = ?1 ", nativeQuery = true)
    void deleteById(String id);

    @Transactional
    @Modifying
    @Query(value = "delete from `order` where orderStatus = '5' and weixin is null", nativeQuery = true)
    void closeOrder();
}
